﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class UserInfoDAL : IUserInfoDAL
    {
      //////////////////////////////////////////
      //  private int _UI_userId;
      //  private string _UI_userName;
      //  private string _UI_password;
      //  private string _UI_mailAddr;
      //  private string _UI_nickName;
      //  private int _UI_gender;
      //  private System.DateTime _UI_birthday;
      //  private string _UI_qqNumber;
      //  private string _UI_phoneNumber;
      //  private string _UI_country;
     //   private string _UI_province;
     //   private string _UI_city;
      //  private System.Nullable<System.DateTime> _UI_regTime;
      //  private string _UI_remark;
      /////////////////////////////////////////


        // Static constants
        private const string SQL_CONN_SQLSERVER = "Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True";
        private const string SQL_SELECT_USERINFOS = "SELECT UI_userId, UI_userName, UI_password, UI_mailAddr, UI_nickName, UI_gender, UI_birthday, UI_qqNumber, UI_phoneNumber, UI_country, UI_province, UI_city, UI_regTime, UI_remark FROM UserInfo";
        private const string SQL_SELECT_USERINFO1 = "SELECT UI_userId, UI_userName, UI_password, UI_mailAddr, UI_nickName, UI_gender, UI_birthday, UI_qqNumber, UI_phoneNumber, UI_country, UI_province, UI_city, UI_regTime, UI_remark  FROM UserInfo WHERE UI_userId = @userId";
        private const string SQL_SELECT_USERINFO2 = "SELECT UI_userId, UI_userName, UI_password, UI_mailAddr, UI_nickName, UI_gender, UI_birthday, UI_qqNumber, UI_phoneNumber, UI_country, UI_province, UI_city, UI_regTime, UI_remark  FROM UserInfo WHERE UI_userName = @userName";
        private const string SQL_INSERT_USERINFO = "INSERT INTO UserInfo VALUES(@userName, @password, @mailAddr, @nickName, @gender, @birthday, @qqNumber, @phoneNumber, @country, @province, @city, @regTime, @remark)";
        private const string SQL_DELETE_USERINFO = "DELETEUI_userId, UI_userName, UI_password, UI_mailAddr, UI_nickName, UI_gender, UI_birthday, UI_qqNumber, UI_phoneNumber, UI_country, UI_province, UI_city, UI_regTime, UI_remark  FROM UserInfo WHERE UI_userId = @userId";
        private const string SQL_UPDATE_USERINFO = "UPDATE UserInfo SET  UI_userName = @userName, UI_password = @password, UI_mailAddr = @mailAddr, UI_nickName = @nickName, UI_gender = @gender, UI_birthday = @birthday, UI_qqNumber = @qqNumber, UI_phoneNumber = @phoneNumber, UI_country = @country, UI_province = @province, UI_city = @city, UI_regTime = @regTime, UI_remark = @remark WHERE UI_userId = @userId";

        /// <summary>
        /// 增加一个userInfor
        /// </summary>
        public bool Add(Model.UserInfo user)
        {

            bool opetation = false;
            Model.UserInfo userInfo = user;

             try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userName", userInfo.getUI_userName()), new SqlParameter("@password", userInfo.getUI_password()), new SqlParameter("@mailAddr", userInfo.getUI_mailAddr()), new SqlParameter("@nickName", userInfo.getUI_nickName()), new SqlParameter("@gender", userInfo.getUI_gender()), new SqlParameter("@birthday", userInfo.getUI_birthday()), new SqlParameter("@qqNumber", userInfo.getUI_qqNumber()), new SqlParameter("@phoneNumber", userInfo.getUI_phoneNumber()), new SqlParameter("@country", userInfo.getUI_country()), new SqlParameter("@province", userInfo.getUI_province()), new SqlParameter("@city", userInfo.getUI_city()), new SqlParameter("@regTime", userInfo.getUI_regTime()), new SqlParameter("@remark", userInfo.getUI_remark()) };



                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_USERINFO, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }catch(Exception ex){
                throw ex;
            }
             return opetation;     
        }


        /// <summary>
        /// 删除一个userInfor通过ID
        /// </summary>
        public bool UserDeleteById(int userId)
        {

            bool opetation = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_DELETE_USERINFO, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 修改一个userInfor
        /// </summary>
        public bool Update(Model.UserInfo user)
        {
            //Set up a return value
            bool opetation = false;
            Model.UserInfo UserInfo = user;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userName", UserInfo.getUI_userName()), new SqlParameter("@password", UserInfo.getUI_password()), new SqlParameter("@mailAddr", UserInfo.getUI_mailAddr()), new SqlParameter("@nickName", UserInfo.getUI_nickName()), new SqlParameter("@gender", UserInfo.getUI_gender()), new SqlParameter("@birthday", UserInfo.getUI_birthday()), new SqlParameter("@qqNumber", UserInfo.getUI_qqNumber()), new SqlParameter("@phoneNumber", UserInfo.getUI_phoneNumber()), new SqlParameter("@country", UserInfo.getUI_country()), new SqlParameter("@province", UserInfo.getUI_province()), new SqlParameter("@city", UserInfo.getUI_city()), new SqlParameter("@regTime", UserInfo.getUI_regTime()), new SqlParameter("@remark", UserInfo.getUI_remark()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_UPDATE_USERINFO, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }


        /// <summary>
        /// 查询一个userInfor通过Id,并返回对象
        /// </summary>
        public Model.UserInfo userIdUserInfo(int userId)
        {
            //Set up a return value
            Model.UserInfo user = null;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_USERINFO1, para))
                {
                    if (rdr.Read())

                        user = new Model.UserInfo(Convert.ToInt32(rdr.GetString(0)), Convert.ToString(rdr.GetString(1)), Convert.ToString(rdr.GetString(2)), Convert.ToString(rdr.GetString(3)), Convert.ToString(rdr.GetString(4)), Convert.ToInt32(rdr.GetString(5)), Convert.ToDateTime(rdr.GetString(6)), Convert.ToString(rdr.GetString(7)), Convert.ToString(rdr.GetString(8)), Convert.ToString(rdr.GetString(9)), Convert.ToString(rdr.GetString(10)), Convert.ToString(rdr.GetString(11)), Convert.ToDateTime(rdr.GetString(12)), Convert.ToString(rdr.GetString(13)));
                    else
                        user = new Model.UserInfo();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return user;
        }


        /// <summary>
        /// 查询一个userInfor通过name,并返回对象
        /// </summary>
        public Model.UserInfo userNameUserInfo(string userName)
        {
            //Set up a return value
            Model.UserInfo user = null;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userName", userName);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_USERINFO2, para))
                {
                    if (rdr.Read())

                        user = new Model.UserInfo(Convert.ToInt32(rdr.GetString(0)), Convert.ToString(rdr.GetString(1)), Convert.ToString(rdr.GetString(2)), Convert.ToString(rdr.GetString(3)), Convert.ToString(rdr.GetString(4)), Convert.ToInt32(rdr.GetString(5)), Convert.ToDateTime(rdr.GetString(6)), Convert.ToString(rdr.GetString(7)), Convert.ToString(rdr.GetString(8)), Convert.ToString(rdr.GetString(9)), Convert.ToString(rdr.GetString(10)), Convert.ToString(rdr.GetString(11)), Convert.ToDateTime(rdr.GetString(12)), Convert.ToString(rdr.GetString(13)));
                    else
                        user = new Model.UserInfo();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return user;
            
        }
    }
}
